package zy.dao.base.shop.impl;

import java.util.List;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.base.shop.ShopLineDAO;
import zy.entity.base.shop.T_Base_Shop_Line;
import zy.util.StringUtil;

@Repository
public class ShopLineDAOImpl extends BaseDaoImpl implements ShopLineDAO{
	@Override
	public T_Base_Shop_Line load(String sp_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" sp_id,sp_code,sp_name,sp_spell,sp_shop_type,sp_state,sp_end,sp_lock,sp_upcode,sp_rate,sp_init,sp_main,sp_init_debt,sp_receivable,sp_received,sp_prepay,sp_sort_cycle,sp_settle_cycle,t.companyid,");
		sql.append(" spi_id,spi_man,spi_tel,spi_mobile,spi_addr,spi_province,spi_city,spi_town,spi_remark,spi_shop_code,");
		sql.append(" spl_id,spl_shop_code,spl_ordershop_code,spl_project,spl_logo,spl_office_hour,spl_longitude,spl_latitude,spl_state,spl_sysdate,");
		sql.append(" (SELECT sp.sp_name FROM t_base_shop sp WHERE sp.sp_code = spl_ordershop_code AND sp.companyid = t.companyid LIMIT 1) AS ordershop_name");
		sql.append(" FROM t_base_shop t");
		sql.append(" JOIN t_base_shop_info spi ON spi_shop_code = sp_code AND spi.companyid = t.companyid");
		sql.append(" LEFT JOIN t_base_shop_line spl ON spl_shop_code = sp_code AND spl.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND sp_code = :sp_code");
		sql.append(" AND t.companyid = :companyid");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("sp_code", sp_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Base_Shop_Line.class));
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public void save(T_Base_Shop_Line shopLine) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" INSERT INTO t_base_shop_line");
		sql.append(" (spl_shop_code,spl_ordershop_code,spl_project,spl_logo,spl_office_hour,spl_longitude,spl_latitude,spl_state,spl_sysdate,companyid)");
		sql.append(" VALUES(:spl_shop_code,:spl_ordershop_code,:spl_project,:spl_logo,:spl_office_hour,:spl_longitude,:spl_latitude,:spl_state,:spl_sysdate,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(shopLine),holder);
		shopLine.setSpl_id(holder.getKey().intValue());
	}
	
	@Override
	public void update(T_Base_Shop_Line shopLine) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_base_shop_line");
		sql.append(" SET spl_ordershop_code = :spl_ordershop_code");
		sql.append(" ,spl_project = :spl_project");
		sql.append(" ,spl_office_hour = :spl_office_hour");
		sql.append(" ,spl_longitude = :spl_longitude");
		sql.append(" ,spl_latitude = :spl_latitude");
		if(StringUtil.isNotEmpty(shopLine.getSpl_logo())){
			sql.append(" ,spl_logo = :spl_logo");
		}
		sql.append(" WHERE spl_id=:spl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(shopLine));
	}
	
	@Override
	public List<T_Base_Shop_Line> listByMobile(String mobile) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" sp_id,sp.sp_code,sp_name,sp_spell,sp_shop_type,sp_state,sp_end,sp_lock,sp_upcode,");
		sql.append(" spl_id,spl_shop_code,spl_ordershop_code,spl_project,spl_logo,spl_office_hour,spl_longitude,spl_latitude,spl_state,t.companyid");
		sql.append(" FROM t_base_shop_line t");
		sql.append(" JOIN t_base_shop sp ON sp_code = spl_shop_code AND sp.companyid = t.companyid");
		sql.append(" JOIN (");
		sql.append(" SELECT (CASE WHEN sp_shop_type = 3 THEN sp_upcode ELSE sp_code END) AS sp_code,vm.companyid");
		sql.append(" FROM t_vip_member vm");
		sql.append(" JOIN t_base_shop sp ON sp_code = vm_shop_code AND sp.companyid = vm.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND vm_mobile = :mobile");
		sql.append(" )temp");
		sql.append(" ON temp.sp_code = t.spl_shop_code AND temp.companyid = t.companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("mobile", mobile), 
				new BeanPropertyRowMapper<>(T_Base_Shop_Line.class));
	}
}
